﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace ALDIOrganizer
{
    // This part of the class contains methods that update records in the CurrentFile
    public static partial class SQLiteAccess
    {
        internal static void SetOnVisa(
            bool pNewValue,
            Int64 pEmployeeID)
        {
            var row = SpawnEmployeeRow();
            row.AsEmployee().OnVisa = pNewValue;
            UpdateEmployee(
                pEmployeeID,
                row,
                true);                
        }

        internal static void UpdateEmployee(Int64 EmpID, DataRow newvalues, bool pJustOnVisa)
        {
            if (!newvalues.MatchesScheme(_dataset.Tables[0].Columns))
            {
                return;
            }

                       
            var emp = GetEmployee(EmpID);
            if (emp == null)
            {
                return;
            }

            emp.BeginEdit();            
            // column 0 is primary key. columns 9+ are calculated columns, we don't touch them.
            if (!pJustOnVisa)
            {
                for (int col = 1; col < 9; col++)
                {
                    emp[col] = newvalues[col];
                }
            }
            emp[16] = newvalues[16]; // onvisa

            emp.EndEdit();

            var builder = new SQLiteCommandBuilder(_adapter);

            var update = new SQLiteCommand(_connection)
                             {
                                 CommandText = @"UPDATE employees " +
                                               @"SET firstName = @firstName, " +
                                               @"lastName = @lastName, " +
                                               @"phone = @phone, " +
                                               @"mobile = @mobile, " +
                                               @"birthDate = @birthDate, " +
                                               @"sex = @sex, " +
                                               @"address = @address, " +
                                               @"nin = @nin, " +
                                               @"onvisa = @onvisa " +
                                               @"WHERE id = '" + EmpID + "' "
                             };

            update.Parameters.AddWithValue("firstName", emp[1]);
            update.Parameters.AddWithValue("lastName", emp[2]);
            update.Parameters.AddWithValue("phone", emp[3]);
            update.Parameters.AddWithValue("mobile", emp[4]);

            update.Parameters.AddWithValue("birthDate", emp[5]);
            update.Parameters.AddWithValue("sex", (bool)emp[6] ? 1 : 0);
            update.Parameters.AddWithValue("address", emp[7]);
            update.Parameters.AddWithValue("nin", emp[8]);
            update.Parameters.AddWithValue("onvisa", new Employee(emp).OnVisa ? 1 : 0);


            _adapter.UpdateCommand = update;
            _adapter.SelectCommand = new SQLiteCommand("SELECT * FROM employees", _connection);
            _adapter.Update(_dataset.Tables[0].GetChanges(DataRowState.Modified));

            _dataset.Tables[0].AcceptChanges();
            OnDataChanged(
                EmpID,
                new [] { 0 },
                (DateTime?)null,
                (DateTime?)null);
        }

        internal static void UpdateContract(Int64 ContractID, DataRow newvalues)
        {
            if (!newvalues.MatchesScheme(_dataset.Tables[1].Columns)) return;

            var con = GetContract(ContractID);
            if (con == null) return;
            con.BeginEdit();
            // column 1 contains the foreign key Value (_employee id). modifying it is not supported. you cannot "pass" the _contract to someone else.
            for (var col = 1; col < 8; col++)
            {
                con[col] = newvalues[col];
            }
            con.EndEdit();

            var builder = new SQLiteCommandBuilder(_adapter);
            var update = new SQLiteCommand(_connection)
                             {
                                 CommandText = @"UPDATE contracts " +
                                               @"SET date = @date, " +
                                               @"hired = @hired, " +
                                               @"position = @position, " +
                                               @"contractHRS = @hours, " +
                                               @"holidayAllowance = @holidays, " +
                                               @"comments = @comments " +
                                               @"WHERE id = '" +
                                               ContractID.ToString() +
                                               "'"
                             };

            update.Parameters.AddWithValue("date", con[2]);
            update.Parameters.AddWithValue("hired", (bool)con[3] ? 1 : 0);
            update.Parameters.AddWithValue("position", con[4]);
            update.Parameters.AddWithValue("hours", con[5]);
            update.Parameters.AddWithValue("holidays", con[6]);
            update.Parameters.AddWithValue("comments", con[7]);

            _adapter.UpdateCommand = update;
            _adapter.SelectCommand = new SQLiteCommand("SELECT * FROM contracts", _connection);
            _adapter.Update(_dataset.Tables[1].GetChanges(DataRowState.Modified));

            _dataset.Tables[1].AcceptChanges();
            var id = (Int64)newvalues[1];
            OnDataChanged(id,
                new int[] { 0, 1},
                (DateTime?)con[2],
                (DateTime?)null);
        }

        internal static void UpdateHolidays(Int64 HolID, DataRow newvalues)
        {
            if (!newvalues.MatchesScheme(_dataset.Tables[2].Columns)) return;

            var hol = GetHolidays(HolID);
            if (hol == null) return;
            
            hol.BeginEdit();

            for (int col = 1; col < 7; col++) hol[col] = newvalues[col];
            hol.EndEdit();
            

            var builder = new SQLiteCommandBuilder(_adapter);
            var update = new SQLiteCommand(_connection)
                             {
                                 CommandText = @"UPDATE holidays " +
                                               @"SET startDate = @startDate, " +
                                               @"endDate = @endDate, " +
                                               @"type = @type, " +
                                               @"pending = @pending, " +
                                               @"comments = @comments " +
                                               @"WHERE id = '" +
                                               HolID +
                                               "'"
                             };

            update.Parameters.AddWithValue("startDate", hol[2]);
            update.Parameters.AddWithValue("endDate", hol[3]);
            update.Parameters.AddWithValue("type", hol[4]);
            update.Parameters.AddWithValue("pending", (bool)hol[5] ? 1 : 0);
            update.Parameters.AddWithValue("comments", hol[6]);

            _adapter.UpdateCommand = update;
            _adapter.SelectCommand = new SQLiteCommand("SELECT * FROM holidays", _connection);
            _adapter.Update(_dataset.Tables[2].GetChanges(DataRowState.Modified));
            
            _dataset.Tables[2].AcceptChanges();
            OnDataChanged((Int64)newvalues[1],
                new[] { 2 },
                (DateTime?)hol[2],
                (DateTime?)hol[3]);
        }

        internal static void UpdateRequest(Int64 RequestID, DataRow newvalues)
        {
            if (!newvalues.MatchesScheme(_dataset.Tables[3].Columns))
                return;

            var request = GetRequest(RequestID);
            if (request == null)
            {
                return;
            }
            request.BeginEdit();
            for (int col = 1; col < 9; col++)
            {
                request[col] = newvalues[col];
            }
            request.EndEdit();

            var _builder = new SQLiteCommandBuilder(_adapter);
            var _update = new SQLiteCommand(_connection)
                              {
                                  CommandText = string.Format(@"UPDATE requests SET startDate = @startDate, endDate = @endDate, startHour = @startHour, endHour = @endHour, weekdays = @weekdays, pending = @pending, comments = @comments WHERE id = '{0}'", RequestID.ToString())
                              };

            var req = new Request(request);

            _update.Parameters.AddWithValue("startDate", req.startDate);
            _update.Parameters.AddWithValue("endDate", req.endDate);
            _update.Parameters.AddWithValue("startHour", req.ForbiddenBeforeHour.HasValue ?
                req.ForbiddenBeforeHour.Value.Time :
                0);
            _update.Parameters.AddWithValue("endHour", req.ForbiddenAfterHour.HasValue ?
                req.ForbiddenAfterHour.Value.Time :
                0);
            _update.Parameters.AddWithValue("weekdays", req.Days.ToFlagInt());
            _update.Parameters.AddWithValue("pending", req.Pending);
            _update.Parameters.AddWithValue("comments", req.Comments);

            _adapter.UpdateCommand = _update;
            _adapter.SelectCommand = new SQLiteCommand(
                "SELECT * FROM requests",
                _connection);
            _adapter.Update(_dataset.Tables[3].GetChanges(DataRowState.Modified));

            _dataset.Tables[3].AcceptChanges();
            
            OnDataChanged(
                (Int64)newvalues[1],
                new[] { 3 },
                req.startDate,
                req.endDate);
            
        }
    }
}
